﻿Imports System.Data.SqlClient
Imports System.Text

Module modDatabase
    Public Structure BulkCopyInfo
        Public Tables As ListView.ListViewItemCollection
        Public SourceCN As SqlConnection
        Public DestinationCN As SqlConnection
    End Structure

    Public Property GlobalSourceConnectionString As String
    Public Property GlobalDestinationConnectionString As String

    Public Function ConnectSQL(connStr As String, ByRef errMsg As String) As SqlConnection
        Try
            Dim cn As New SqlConnection(connStr)
            cn.Open()
            Return cn
        Catch ex As Exception
            errMsg = ex.Message
            WriteLog(LogEventType.Kesalahan, "Terjadi masalah saat melakukan koneksi database.", ex)
            Return Nothing
        End Try
    End Function

    Public Function IsTableExists(conn As SqlConnection, tableName As String) As Boolean
        Using cmd As New SqlCommand("SELECT * FROM sys.tables WHERE [name] = '" & tableName & "' AND [type] = 'U'", conn)
            Using rd As SqlDataReader = cmd.ExecuteReader
                Return rd.HasRows
            End Using
        End Using
    End Function

    Public Function CountTableRows(conn As SqlConnection, tableName As String) As Long
        Using cmd As New SqlCommand("SELECT CONVERT(bigint , rows) FROM sysindexes WHERE id = OBJECT_ID('" & tableName & "') AND indid < 2", conn)
            Return cmd.ExecuteScalar
        End Using
    End Function

    Public Sub DropAndCreateTable(sourceCN As SqlConnection, sourceTable As String, destCN As SqlConnection, destTable As String)
        Using cmd As New SqlCommand("SELECT TOP 1 FROM [dbo].[" & sourceTable & "]", sourceCN)
            Dim rd As SqlDataReader = cmd.ExecuteReader
            Dim temp_dt As DataTable = rd.GetSchemaTable

            

        End Using
    End Sub

    Public Function CreateTableSQL(tbl As DataTable, tableName As String)
        Dim final_Query As New System.Text.StringBuilder
        With final_Query
            .Append("CREATE TABLE ")
            .Append(tableName)
            .Append(" (")
            For Each dtr As DataColumn In tbl.Columns
                Dim isNumberic As Boolean = False
                Dim isDefault As Boolean = True

                .Append(vbLf & vbTab & "[" & dtr.ColumnName & "]")
                Select Case dtr.DataType.ToString
                    Case "System.Int32"
                        isNumberic = True
                        .Append(" int ")
                    Case "System.DateTime"
                        isDefault = False
                        .Append(" datetime ")
                    Case "System.String"
                        .Append(" varchar(" & dtr.MaxLength & ") ")
                    Case "System.Int16"
                        isNumberic = True
                        .Append(" smallint ")
                    Case "System.Int64"
                        isNumberic = True
                        .Append(" bigint ")
                    Case "System.Decimal"
                        isNumberic = True
                        .Append(" decimal(18, 6) ")
                    Case "System.Double"
                        isNumberic = True
                        .Append(" double ")
                    Case "System.Single"
                        isNumberic = True
                        .Append(" single ")
                    Case Else
                        .Append(" varchar(" & dtr.MaxLength & ") ")
                End Select
                If Not dtr.AllowDBNull Then
                    .Append(" NOT NULL ")
                End If
                If dtr.AutoIncrement Then
                    .Append(" IDENTITY(" & dtr.AutoIncrementSeed & "," & dtr.AutoIncrementStep & ") ")
                End If
                .Append(",")
            Next
            .Remove(.Length - 1, 1)
            .Append(")")

            If tbl.PrimaryKey.Count > 0 Then
                Dim pks As String = vbCrLf & "CONSTRAINT PK_" + tableName + " PRIMARY KEY ("
                Dim i As Integer = 0
                While i < tbl.PrimaryKey.Length
                    pks += tbl.PrimaryKey(i).ColumnName + ","
                End While
                pks = pks.Substring(0, pks.Length - 1)
                .Append(pks & ")")
            End If
        End With
        Return final_Query.ToString
    End Function

End Module
